# coding: utf-8

# NOTE by zhouhl 2018-07-24
# 核对充值过的用户的汇币消耗，与当前账户剩余汇币

# BidCostType
# 入(+)
# (2, '充值')
# (3, '未联系退还')
# (6, '反馈退还')
# (15, '转账收款')

# 出(-)
# (1, '抢单')
# (4, '未联系惩罚')
# (7, '订单锁定')
# (14, '转账汇款')

import os
import datetime
import math
import set_env_path

from sqlalchemy import create_engine
from sqlalchemy.sql import text

from common.wechat_util import update_users_tag

engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def main():
    # 骨灰用户ids
    long_long_time_ago_user_ids = [
        3059152, 36832, 48284, 3053380, 53369, 3043669, 102448, 3074133,
        72797, 128132, 131220, 114661, 3045207, 36159, 3074372, 3123620,
        3052115, 3096249, 3044678, 3047474, 41088, 104912, 3046014,
        29829, 23246
    ]

    # result结构体:
    # {
    #     'user_id1':{
    #         'pack_bid_point': 123,
    #         'sum_amt': 1000,
    #         'bid_cost': 900,
    #     },
    #     ...
    # }
    result = {}

    # 充过值的工程商的充值总额
    get_sum_amt_sql = text("""
        select
            uid, sum(amt) as sum_amt
        from
            bill
        where
            type=1
            and sub_type in (2,3)
            and status = 200
        group by
            uid
        having
            sum_amt>0;
    """)

    # 查询指定用户的汇币余额
    get_pack_bid_point_sql = text("""
        select
            bid_point
        from
            pack
        where
            uid=:user_id;
    """)

    # 充过值工程商的汇币消耗记录
    get_bid_cost_record_sql = text("""
        select
            user_id, cost_type, bid_point
        from
            bid_cost_record
        where
            user_id in (
                select DISTINCT uid from bill where
                    bill.type = 1
                    and bill.sub_type in ( 2, 3 )
                    and bill.status = 200
            )
            and cost_type in (2,3,6,15,1,4,7,14);
    """)

    tmp_result = conn.execute(get_sum_amt_sql).fetchall()
    for i in tmp_result:
        if i.uid in long_long_time_ago_user_ids:
            continue
        tmp_result_2 = conn.execute(
            get_pack_bid_point_sql,
            user_id=i.uid,
        ).fetchone()
        result[i.uid] = {
            'sum_amt': i.sum_amt,
            'pack_bid_point': tmp_result_2.bid_point,
            'bid_cost' : 0,
        }

    tmp_result = conn.execute(get_bid_cost_record_sql).fetchall()
    for i in tmp_result:
        if i.user_id in result:
            if i.cost_type in (2,3,6,15):
                result[i.user_id]['bid_cost'] += i.bid_point
            elif i.cost_type in (1,4,7,14):
                result[i.user_id]['bid_cost'] -= i.bid_point

    print(','.join(
        ['user_id','sum_amt','bid_cost','pack_bid_point']
    ))
    for i in result:
        print(','.join(
            map(str,[
                i,
                result[i]['sum_amt'],
                result[i]['bid_cost'],
                result[i]['pack_bid_point'],
            ])
        ))


if __name__ == '__main__':
    main()
